﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ForumDomainModels;
using System.Data.SqlClient;

namespace ForumDAL
{
    public class UserDAL
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int GetUserCountWithSpecifiedUsername(ForumDomainModels.User user)
        {
            string connectionString = Properties.Settings.Default.connectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT Count(*) FROM [ForumDb].[dbo].[Users] WHERE Username = @username";
                using (SqlCommand command = new SqlCommand(sql,connection))
                {
                    command.Parameters.Add(new SqlParameter("username",user.Username));
                    int userCount = (int)command.ExecuteScalar();
                    return userCount;
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int GetUserCountWithSpecifiedUsernameAndPassword(ForumDomainModels.User user)
        {
            string connectionString = Properties.Settings.Default.connectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT Count(*) FROM [ForumDb].[dbo].[Users] "+
                    "WHERE Username = @username and Password = @password";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.Add(new SqlParameter("username", user.Username));
                    command.Parameters.Add(new SqlParameter("password", user.Password));
                    int userCount = (int)command.ExecuteScalar();
                    return userCount;
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public ForumDomainModels.User GetUserWithSpecifiedUsernameAndPassword(ForumDomainModels.User user)
        {
            string connectionString = Properties.Settings.Default.connectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT Id,[Username],[Password],[FirstName],[LastName],[Age],[Photo],[IsAdmin],[DateRegistered] "+
                    "FROM [ForumDb].[dbo].[Users] "+
                    "WHERE Username = @username and Password = @password";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.Add(new SqlParameter("username", user.Username));
                    command.Parameters.Add(new SqlParameter("password", user.Password));
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (!reader.HasRows)
                        {
                            throw new CustomExceptions.NoRowsRetrieved();
                        }
                        else
                        {
                            reader.Read();
                            ForumDomainModels.Mappers.UserMapper userMapper = new ForumDomainModels.Mappers.UserMapper();
                            ForumDomainModels.User userModel = userMapper.DataFromDatabaseToUserDomainModel(reader);
                            return userModel;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="user"></param>
        public void InsertUser(ForumDomainModels.User user)
        {
            string connectionString = Properties.Settings.Default.connectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "INSERT INTO Users ([Username],[Password],[FirstName],[LastName],[Age],[DateRegistered],[IsAdmin]) " +
                    "VALUES(@username,@password,@firstName,@lastName,@age,@dateRegistered,@isAdmin)";

                using (SqlCommand command = new SqlCommand(sql,connection))
                {   
                    command.Parameters.Add( new SqlParameter("username",user.Username));
                    command.Parameters.Add( new SqlParameter("password",user.Password));
                    command.Parameters.Add( new SqlParameter("firstName",user.FirstName));
                    command.Parameters.Add( new SqlParameter("lastName",user.LastName));
                    command.Parameters.Add( new SqlParameter("age",user.Age));
                    command.Parameters.Add( new SqlParameter("dateRegistered",user.DateRegistered));
                    command.Parameters.Add( new SqlParameter("isAdmin",user.IsAdmin));

                    int affectedRows = command.ExecuteNonQuery();
                    if (affectedRows == 0)
                    {
                        throw new CustomExceptions.NotInsertedRowsInDatabase();
                    }
                }
            }
        }



        public User GetUserById(User user)
        {
            string connectionString = Properties.Settings.Default.connectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT [Id], [Username], [Password], [FirstName], [LastName],[Age], [Photo], [IsAdmin], [DateRegistered] "+
                    "FROM [ForumDb].[dbo].[Users] "+
                    "WHERE Id = @id";
                using (SqlCommand command = new SqlCommand(sql,connection))
                {
                    command.Parameters.Add(new SqlParameter("id",user.Id));
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (!reader.HasRows)
                        {
                            throw new CustomExceptions.NoRowsRetrieved();
                        }
                        else
                        {
                            ForumDomainModels.Mappers.UserMapper userMapper =
                                new ForumDomainModels.Mappers.UserMapper();
                            reader.Read();
                           ForumDomainModels.User retrievedUser =  
                               userMapper.DataFromDatabaseToUserDomainModel(reader);
                           return retrievedUser;
                        }
                    }
                }
            }
        }
    }
}
